package tw.com.softleader.reins.demo.coffee.service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import tw.com.softleader.reins.demo.coffee.entity.Coffee;

public class CoffeeService {
	
	
	private static final String URL ="jdbc:postgresql://localhost:5432/postgres" ;
	private static final String USER = "postgres";
	private static final String PASSWORD = "admin";

	private static final String SELECT_BY_ID = 
			"select * from coffee where id = ?";
	private static final String SELECT_ALL = 
			"select * from coffee";
	
	List<Coffee> coffeeList= new ArrayList<Coffee>();
	public CoffeeService(){
		
	}
	
public void select() throws Exception {
		
		
		Connection conn= CoffeeService.connection();
		PreparedStatement pstmt = conn.prepareStatement(SELECT_ALL);
		
		ResultSet rs = pstmt.executeQuery();
		
//		String name =null, date=null;
//		double price=0;
//		int id=0,expire=0;
		
	    String brand;
		String size;
		String type;
		Long id;
		Double price;
		
		//List<Coffee> productList = new ArrayList<Coffee>();
		
		while(rs.next()) {
			id= rs.getLong(1);
			brand = rs.getString(2);
			size=rs.getString(3);
			type=rs.getString(4);
			price= rs.getDouble(5);

			Coffee result = new Coffee();
			result.setId(id);
			result.setBrand(brand);
			result.setType(type);
			result.setPrice(price);
			

			
			coffeeList.add(result);//put one coffee information into the coffee list 


			
		}

		
	

		if(conn!=null){
			try {
				conn.close();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		

	
		



	}
	
	
//	private void insert() {
//		Coffee coffee1=new Coffee();
//		coffee1.setBrand("StarBucks");
//		coffee1.setId(1L);
//		coffee1.setPrice(105.0);
//		coffee1.setType("Latte");
//		coffee1.setSize("M");
//	
//		coffeeList.add(coffee1);
//		
//		Coffee coffee2=new Coffee();
//		coffee2.setBrand("StarBucks");
//		coffee2.setId(2L);
//		coffee2.setPrice(90.0);
//		coffee2.setType("Cappuccino");
//		coffee2.setSize("M");
//		
//		coffeeList.add(coffee2);
//	}
	
	public void getAll() throws Exception{
		
		this.select();//use select * from coffee to get all the data of coffee in the database
		System.out.println("List all");
		this.printCoffeeForm();
		
		for(Coffee coffee: coffeeList){
			
			
			this.printCoffeeRow(coffee);
		}
		
		System.out.println("---");
		System.out.println();
		
	}
	
	public void getByID(Long id){
		
		System.out.println("Get by ID");
		
		
		for(Coffee coffee:coffeeList){
			if(coffee.getId()==id){
				System.out.println(coffee.toString());
			}
		}
		
		System.out.println("---");
		System.out.println();
		
		
		
	}
	
	public void printCoffeeRow(Coffee coffee) {
		System.out.printf("%2d %20s %20s %10s %10.2f", 
				coffee.getId(), coffee.getBrand(), coffee.getType(), coffee.getSize(), coffee.getPrice());
		System.out.println();
		
	}
	public void printCoffeeForm() {
		System.out.printf("%2s %20s %20s %10s %10s","ID", "Brand", "Type", "size","price");
		System.out.println();
		System.out.println("-------------------------------------------------------------------");
		
	}
	
	private static Connection connection() throws ClassNotFoundException, SQLException{
		Connection conn=null;
		
		Class.forName("org.postgresql.Driver");
		String connUrl = URL;
		conn = DriverManager.getConnection(connUrl, USER, PASSWORD);

		return conn;
	}
	
	public static void main(String[] args) throws Exception {
		CoffeeService coffeeDAO= new CoffeeService();
		
		
		//test select;
//		coffeeDAO.select();
//		coffeeDAO.printCoffeeForm();
//		for(Coffee coffee:coffeeDAO.coffeeList){
//			coffeeDAO.printCoffeeRow(coffee);
//		}
		
		//coffeeDAO.getAll();


	}
	

}
